package lazy.tool;

import java.io.*;
import java.sql.*;
import java.util.*;
import lazy.sql.*;
import lazy.util.*;

/**
 * 使用範例：<br/>
 * BeanGenerator.genBean(DriverName.DRIVER_NAME_JTDS_DRIVER, new JdbcUrl(DBType.MSSQLServer, "192.168.123.33", "cos_ok"), "sa", "@satku74123698@", "C:/", "tw.com.ebti");
 * @author Tim
 */
public class BeanGenerator {
	private static boolean isPrimaryKey(String driverName, String jdbcUrl, String userName, String password, String tableName, String columnName, DBType dbType) {
		boolean isPrimaryKey = false;

		if(StringUtils.hasText(driverName)
		&& StringUtils.hasText(jdbcUrl)
		&& StringUtils.hasText(userName)
		&& StringUtils.hasText(password)
		&& StringUtils.hasText(tableName)
		&& StringUtils.hasText(columnName)
		&& dbType!=null) {
			Connection con = null;
			PreparedStatement stmt = null;
			ResultSet rs = null;
			String sql = null;
			switch(dbType) {
				case MSSQLServer:
					sql = "select count(*) as a from information_schema.key_column_usage join sys.key_constraints on [name]=constraint_name where [type]='pk' and table_name=? and column_name=?";
					break;
				case MySQL:
					sql = "select count(*) as a from information_schema.key_column_usage where constraint_name='primary' and table_name=? and column_name=?";
					break;
			}
			if(StringUtils.hasText(sql)) {
				try {
					con = DriverManager.getConnection(jdbcUrl, userName, password);
					stmt = con.prepareStatement(sql);
					stmt.setString(1, tableName);
					stmt.setString(2, columnName);
					rs = stmt.executeQuery();
					if(rs.next()) {
						isPrimaryKey = rs.getInt("a")>=1;
					}
				} catch (SQLException e) {
					e.printStackTrace();
				} finally {
					close(rs, stmt, con, null);
				}
			}
		}
		return isPrimaryKey;
	}
	private static StringBuilder getSrcImports(StringBuilder src, Set<String> imports) {
		if(src==null) {
			src = new StringBuilder();
		}
		for(String s:imports) {
			src.append("import ").append(s).append(";\n");
		}
		return src;
	}
	private static StringBuilder getSrcProperties(StringBuilder src, List<ColumnDefinition> columns) {
		if(src==null) {
			src = new StringBuilder();
		}
		for(ColumnDefinition column:columns) {
			//if(column.isPrimaryKey()) src.append("\t@PK\n");
			src.append("\tprivate ").append(column.getColumnClassName()).append(" ").append(column.getColumnName()).append(";\n");
		}
		return src;
	}
	private static StringBuilder getSrcSql(StringBuilder src, String tableName, List<ColumnDefinition> columns) {
		if(src==null) {
			src = new StringBuilder();
		}
		StringBuilder insert = new StringBuilder("\tpublic static final String INSERT_").append(tableName.toUpperCase()).append(" = \"insert into ").append(tableName).append("(");
		StringBuilder values = new StringBuilder(" values(");
		StringBuilder update = new StringBuilder("\tpublic static final String UPDATE_").append(tableName.toUpperCase()).append(" = \"update ").append(tableName).append(" set ");
		StringBuilder where = null;
		
		for(int i=0;i<columns.size();i++) {
			ColumnDefinition column = columns.get(i);
			insert.append(column.getColumnName());
			values.append("?");
			if(column.isPrimaryKey()) {
				if(where==null) {
					where = new StringBuilder(" where ").append(column.getColumnName()).append("=?");
				} else {
					where.append(" and ").append(column.getColumnName()).append("=?");
				}
			} else {
				update.append(column.getColumnName()).append("=?");
				if(i<columns.size()-1) {
					update.append(", ");
				} else {
					if(where==null) {
						update.append("\";\n");
					} else {
						where.append("\";\n");
					}
				}
			}
			if(i<columns.size()-1) {
				insert.append(", ");
				values.append(", ");
			} else {
				insert.append(")");
				values.append(")\";\n");
			}
		}
		src.append("\tpublic static final String SELECT_ALL_").append(tableName.toUpperCase()).append(" = \"select * from ").append(tableName).append("\";\n");
		src.append(insert);
		src.append(values);
		src.append(update);
		src.append(where);
		return src;
	}
	private static StringBuilder getSrcSetterGetterMethods(StringBuilder src, List<ColumnDefinition> columns) {
		if(src==null) {
			src = new StringBuilder();
		}
		for(ColumnDefinition column:columns) {
			if(column.isPrimaryKey()) src.append("\t@PK\n");
			src.append("\tpublic void ").append(StringUtils.toSetter(column.getColumnName())).append("(").append(column.getColumnClassName()).append(" ").append(column.getColumnName()).append(") {\n");
			src.append("\t\t").append("this.").append(column.getColumnName()).append(" = ").append(column.getColumnName()).append(";\n");
			src.append("\t}\n");
			if(column.isPrimaryKey()) src.append("\t@PK\n");
			src.append("\tpublic ").append(column.getColumnClassName()).append(" ").append(StringUtils.toGetter(column.getColumnName())).append("() {\n");
			src.append("\t\treturn this.").append(column.getColumnName()).append(";\n");
			src.append("\t}\n\n");
		}
		return src;
	}
	private static StringBuilder getSrcMapRow(StringBuilder src, String tableName, List<ColumnDefinition> columns) {
		if(src==null) {
			src = new StringBuilder();
		}
		src.append("\t@Override\n");
		src.append("\tpublic Object mapRow(ResultSet rs, int i) throws SQLException {\n");
		src.append("\t\t").append(tableName).append(" o = new ").append(tableName).append("();\n");
		for(ColumnDefinition column:columns) {
			if("Date".equals(column.getColumnClassName())) {
				column.setColumnClassName("Timestamp");
			} else if("Integer".equals(column.getColumnClassName())) {
				column.setColumnClassName("Int");
			}
			src.append("\t\to.").append(StringUtils.toSetter(column.getColumnName())).append("(rs.get").append(column.getColumnClassName()).append("(").append("\"").append(column.getColumnName()).append("\"));\n");
		}
		src.append("\t\treturn o;\n");
		src.append("\t}\n");
		return src;
	}

	public static void genBean(String driverName, JdbcUrl jdbcUrl, String userName, String password, String fileRoot, String packageName) {
		genBean(driverName, jdbcUrl.getJdbcUrl(), userName, password, fileRoot, packageName, jdbcUrl.getDbType());
	}
	public static void genBean(String driverName, JdbcUrl jdbcUrl, String userName, String password, String fileRoot, String packageName, String[] tableName) {
		genBean(driverName, jdbcUrl.getJdbcUrl(), userName, password, fileRoot, packageName, jdbcUrl.getDbType(), tableName);
	}
	public static void genBean(String driverName, JdbcUrl jdbcUrl, String userName, String password, String fileRoot, String packageName, String tableName) {
		genBean(driverName, jdbcUrl.getJdbcUrl(), userName, password, fileRoot, packageName, jdbcUrl.getDbType(), tableName);
	}
	/**
	 * @deprecated
	 * 請改用genBean(String driverName, JdbcUrl jdbcUrl, String userName, String password, String fileRoot, String packageName, String tableName)
	 * @see JdbcUrl
	 * @param driverName
	 * @param jdbcUrl
	 * @param userName
	 * @param password
	 * @param fileRoot
	 * @param packageName
	 * @param dbType
	 * @param tableName
	 */
	public static void genBean(String driverName, String jdbcUrl, String userName, String password, String fileRoot, String packageName, DBType dbType, String tableName) {
		if(StringUtils.hasText(driverName)
		&& StringUtils.hasText(jdbcUrl)
		&& StringUtils.hasText(userName)
		&& StringUtils.hasText(password)
		&& StringUtils.hasText(fileRoot)
		&& StringUtils.hasText(packageName)
		&& StringUtils.hasText(tableName)) {
			Connection con = null;
			PreparedStatement stmt = null;
			ResultSet rs = null;
			BufferedWriter out = null;
			//
			try {
				final String rootPath = new StringBuilder(fileRoot).append(packageName.replaceAll("\\.", "/")).toString();
				File root = new File(rootPath);
				if(!root.exists()) root.mkdirs();
				//
				Class.forName(driverName);
				con = DriverManager.getConnection(jdbcUrl, userName, password);
				String stmtStr = null;
				switch (dbType) {
					case MySQL:
						stmtStr = new StringBuilder("select * from ").append(tableName).toString();
						break;
					case MSSQLServer:
						stmtStr = new StringBuilder("select * from [").append(tableName).append("]").toString();
						break;
					default:
						stmtStr = new StringBuilder("select * from [").append(tableName).append("]").toString();
						break;
				}
				stmt = con.prepareStatement(stmtStr);
				rs = stmt.executeQuery();
				ResultSetMetaData metaData = rs.getMetaData();
				Set<String> importSrc = new HashSet<String>();
				List<ColumnDefinition> columns = new ArrayList<ColumnDefinition>();
				importSrc.add("ebti.db.annotation.PK");
				for(int i=1;i<=metaData.getColumnCount();i++) {
					String columnClassName = metaData.getColumnClassName(i);
					String columnName = metaData.getColumnName(i);

					if(!columnClassName.startsWith("java.lang")) {
						if("java.sql.Timestamp".equals(columnClassName)
						|| "java.sql.Date".equals(columnClassName)
						|| "java.sql.Time".equals(columnClassName)) {
							columnClassName = "java.util.Date";
							importSrc.add(columnClassName);
						} else if("java.math.BigDecimal".equals(columnClassName)) {
							columnClassName = "java.lang.Double";
						} else if("java.sql.Clob".equals(columnClassName)) {
							columnClassName = "java.lang.String";
						} else {}
					}
					ColumnDefinition column = new ColumnDefinition();
					column.setColumnName(new StringBuilder().append(columnName.substring(0,1).toLowerCase()).append(columnName.substring(1)).toString());
					column.setColumnClassName(StringUtils.toUpperCase(Class.forName(columnClassName).getSimpleName(), 0));
					column.setIsPrimaryKey(isPrimaryKey(driverName, jdbcUrl, userName, password, tableName, columnName, dbType));
					columns.add(column);
				}
				//
				out = new BufferedWriter(new FileWriter(new StringBuilder(rootPath).append("/").append(tableName).append(".java").toString()));
				StringBuilder src = new StringBuilder();
				src.append("package ").append(packageName).append(";\n");
				src.append("\n");
				getSrcImports(src, importSrc);
				src.append("\n");
				src.append("public class ").append(tableName).append(" {\n");
				getSrcProperties(src, columns);
				src.append("\n");
				getSrcSql(src, tableName, columns);
				src.append("\n");
				getSrcSetterGetterMethods(src, columns);
				src.append("}");
				out.write(src.toString());
			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				close(rs, stmt, con, out);
			}
		}
	}
	/**
	 * @deprecated
	 * 請改用genBean(String driverName, JdbcUrl jdbcUrl, String userName, String password, String fileRoot, String packageName, String[] tableName)
	 * @see JdbcUrl
	 * @param driverName
	 * @param jdbcUrl
	 * @param userName
	 * @param password
	 * @param fileRoot
	 * @param packageName
	 * @param dbType
	 * @param tableNames
	 */
	public static void genBean(String driverName, String jdbcUrl, String userName, String password, String fileRoot, String packageName, DBType dbType, String[] tableNames) {
		for(String tableName:tableNames) {
			genBean(driverName, jdbcUrl, userName, password, fileRoot, packageName, dbType, tableName);
		}
	}
	/**
	 * @deprecated
	 * 請改用genBean(String driverName, JdbcUrl jdbcUrl, String userName, String password, String fileRoot, String packageName)
	 * @see JdbcUrl
	 * @param driverName
	 * @param jdbcUrl
	 * @param userName
	 * @param password
	 * @param fileRoot
	 * @param packageName
	 * @param dbType
	 */
	public static void genBean(String driverName, String jdbcUrl, String userName, String password, String fileRoot, String packageName, DBType dbType) {
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		String selectAllTables = null;
		switch(dbType) {
			case MySQL:
				selectAllTables = new StringBuilder("select table_name from information_schema.tables where table_schema = '").append(jdbcUrl.substring(jdbcUrl.lastIndexOf("/")+1).trim()).append("'").toString();
				break;
			case MSSQLServer:
				selectAllTables = "select table_name from information_schema.tables";
				break;
		}
		if(selectAllTables!=null) {
			try {
				Class.forName(driverName);
				con = DriverManager.getConnection(jdbcUrl, userName, password);
				stmt = con.prepareStatement(selectAllTables);
				rs = stmt.executeQuery();
				while(rs.next()) {
					genBean(driverName, jdbcUrl, userName, password, fileRoot, packageName, dbType, rs.getString("table_name"));
				}
			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				close(rs, stmt, con, null);
			}
		}
	}
	/**
	 * @deprecated
	 * 已由lazy.ql.DynaRowMapper取代。
	 * @param driverName
	 * @param jdbcUrl
	 * @param userName
	 * @param password
	 * @param fileRoot
	 * @param packageName
	 * @param dbType
	 * @param tableName
	 * @see DynaRowMapper
	 */
	public static void genRowMapperBean(String driverName, String jdbcUrl, String userName, String password, String fileRoot, String packageName, DBType dbType, String tableName) {
		if(StringUtils.hasText(driverName)
		&& StringUtils.hasText(jdbcUrl)
		&& StringUtils.hasText(userName)
		&& StringUtils.hasText(password)
		&& StringUtils.hasText(fileRoot)
		&& StringUtils.hasText(packageName)
		&& StringUtils.hasText(tableName)) {
			Connection con = null;
			PreparedStatement stmt = null;
			ResultSet rs = null;
			BufferedWriter out = null;
			//
			try {
				final String rootPath = new StringBuilder(fileRoot).append(packageName.replaceAll("\\.", "/")).toString();
				File root = new File(rootPath);
				if(!root.exists()) root.mkdirs();
				//
				Class.forName(driverName);
				con = DriverManager.getConnection(jdbcUrl, userName, password);
				String stmtStr = null;
				switch (dbType) {
					case MySQL:
						stmtStr = new StringBuilder("select * from ").append(tableName).toString();
						break;
					case MSSQLServer:
						stmtStr = new StringBuilder("select * from [").append(tableName).append("]").toString();
						break;
					default:
						stmtStr = new StringBuilder("select * from [").append(tableName).append("]").toString();
						break;
				}
				stmt = con.prepareStatement(stmtStr);
				rs = stmt.executeQuery();
				ResultSetMetaData metaData = rs.getMetaData();
				Set<String> importSrc = new HashSet<String>();
				List<ColumnDefinition> columns = new ArrayList<ColumnDefinition>();
				importSrc.add("org.springframework.jdbc.core.RowMapper");
				importSrc.add("java.sql.ResultSet");
				importSrc.add("java.sql.SQLException");
				importSrc.add("ebti.db.annotation.PK");
				for(int i=1;i<=metaData.getColumnCount();i++) {
					String columnClassName = metaData.getColumnClassName(i);
					String columnName = metaData.getColumnName(i);
					
					if(!columnClassName.startsWith("java.lang")) {
						if("java.sql.Timestamp".equals(columnClassName)
						|| "java.sql.Date".equals(columnClassName)
						|| "java.sql.Time".equals(columnClassName)) {
							columnClassName = "java.util.Date";
							importSrc.add(columnClassName);
						} else if("java.math.BigDecimal".equals(columnClassName)) {
							columnClassName = "java.lang.Double";
						} else if("java.sql.Clob".equals(columnClassName)) {
							columnClassName = "java.lang.String";
						} else {}
					}
					ColumnDefinition column = new ColumnDefinition();
					column.setColumnName(new StringBuilder().append(columnName.substring(0,1).toLowerCase()).append(columnName.substring(1)).toString());
					column.setColumnClassName(StringUtils.toUpperCase(Class.forName(columnClassName).getSimpleName(), 0));
					column.setIsPrimaryKey(isPrimaryKey(driverName, jdbcUrl, userName, password, tableName, columnName, dbType));
					columns.add(column);
				}
				//
				out = new BufferedWriter(new FileWriter(new StringBuilder(rootPath).append("/").append(tableName).append(".java").toString()));
				StringBuilder src = new StringBuilder();
				src.append("package ").append(packageName).append(";\n");
				src.append("\n");
				getSrcImports(src, importSrc);
				src.append("\n");
				src.append("public class ").append(tableName).append(" implements RowMapper {\n");
				getSrcProperties(src, columns);
				src.append("\n");
				getSrcSql(src, tableName, columns);
				src.append("\n");
				getSrcSetterGetterMethods(src, columns);
				getSrcMapRow(src, tableName, columns);
				src.append("}");
				out.write(src.toString());
			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				close(rs, stmt, con, out);
			}
		}
	}
	/**
	 * @deprecated
	 * 已由lazy.ql.DynaRowMapper取代。
	 * @param driverName
	 * @param jdbcUrl
	 * @param userName
	 * @param password
	 * @param fileRoot
	 * @param packageName
	 * @param dbType
	 * @param tableNames
	 * @see DynaRowMapper
	 */
	public static void genRowMapperBean(String driverName, String jdbcUrl, String userName, String password, String fileRoot, String packageName, DBType dbType, String[] tableNames) {
		for(String tableName:tableNames) {
			genRowMapperBean(driverName, jdbcUrl, userName, password, fileRoot, packageName, dbType, tableName);
		}
	}
	/**
	 * @deprecated
	 * 已由lazy.ql.DynaRowMapper取代。
	 * @param driverName
	 * @param jdbcUrl
	 * @param userName
	 * @param password
	 * @param fileRoot
	 * @param packageName
	 * @param dbType
	 * @see DynaRowMapper
	 */
	public static void genRowMapperBean(String driverName, String jdbcUrl, String userName, String password, String fileRoot, String packageName, DBType dbType) {
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		String selectAllTables = null;
		switch(dbType) {
			case MySQL:
				selectAllTables = new StringBuilder("select table_name from information_schema.tables where table_schema = '").append(jdbcUrl.substring(jdbcUrl.lastIndexOf("/")+1).trim()).append("'").toString();
				break;
			case MSSQLServer:
				selectAllTables = "select table_name from information_schema.tables";
				break;
		}
		if(selectAllTables!=null) {
			try {
				Class.forName(driverName);
				con = DriverManager.getConnection(jdbcUrl, userName, password);
				stmt = con.prepareStatement(selectAllTables);
				rs = stmt.executeQuery();
				while(rs.next()) {
					genRowMapperBean(driverName, jdbcUrl, userName, password, fileRoot, packageName, dbType, rs.getString("table_name"));
				}
			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				close(rs, stmt, con, null);
			}
		}
	}
	private static void close(ResultSet resultSet, Statement statement, Connection connection, Writer writer) {
		if (resultSet!=null) {
			try {
				resultSet.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (statement!=null) {
			try {
				statement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (connection!=null) {
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if(writer!=null) {
			try {
				writer.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
	}
}
